﻿// See https://aka.ms/new-console-template for more information

// SqlSugar.MySqlConnector.MySqlConnectorCore
using Newtonsoft.Json;
using SqlSugar;
using SugarDemo.Model;

var Db = new SqlSugarScope(new ConnectionConfig()
{
    ConnectionString = "server=192.168.1.88;Database=testdb;Uid=root;Pwd=8888888;charset=utf8mb4;Character Set=utf8mb4;OldGuids=true",
    DbType = DbType.MySqlConnector,
    IsAutoCloseConnection = true,
    ConfigureExternalServices = new ConfigureExternalServices()
    {
        EntityNameService = (type, entity) =>
        {
            entity.IsDisabledDelete = true;
        }
    }
});

Db.Aop.OnLogExecuting = (sql, pars) =>
{
    var log = sql;
    if (pars != null && pars.Length > 0)
    {
        log += "\n" + string.Join(",", pars.Select(it => it.ParameterName + ":" + it.Value));
    }
    Console.WriteLine(log);
};
Console.WriteLine("是否生成表（Y/N）:");
var input = Console.ReadLine();
if (input == "Y" || input == "y")
{
    Db.CodeFirst.InitTables<SchoolModel, StudentModel>();
}
Console.WriteLine("是否初始化数据（Y/N）:");
input = Console.ReadLine();
if (input == "Y" || input == "y")
{
    var schoolModel = new SchoolModel
    {
        Name = "清华",
    };
    schoolModel.SchoolId = Db.Insertable<SchoolModel>(schoolModel).ExecuteReturnIdentity();
    Db.Insertable<StudentModel>(new List<StudentModel> {
        new StudentModel{
            Name= "张三",
            Hobbies =new List<string>{
            "篮球","爬山"
            },
            SchoolId = schoolModel.SchoolId,
        },
        new StudentModel{
            Name= "李四",
            Hobbies =new List<string>{
            "篮球","爬山"
            },
            SchoolId = schoolModel.SchoolId,
        }
    }).ExecuteCommand();
}

var data1 = await Db.Queryable<StudentModel>()
    .ToPageListAsync(1, 20);

Console.WriteLine("data1={0}", JsonConvert.SerializeObject(data1));

/**
 * 联合表查询json字段Hobbies为null
 * 情况：刚开始json字段特性没有加IsNullable = true，是可以查出json字段数据的，加上之后，就一直为NULL。
 * 以后再把IsNullable = true加上或去掉，就再也查不出数据了
 * **/
var data = await Db.Queryable<StudentModel>().InnerJoin<SchoolModel>((student, school) => student.SchoolId == school.SchoolId)
    .Select((student, school) => new
    {
        StudentId = student.StudentId,
        Hobbies = student.Hobbies,
        SchoolId = school.SchoolId,
        SchoolName = school.Name,
        Name = student.Name,
    }).ToPageListAsync(1, 20);

Console.WriteLine("data={0}", JsonConvert.SerializeObject(data));
